import math
import random
import numpy
import numpy as np
import pymysql
from matplotlib import pyplot as plt
from sklearn.metrics import mean_absolute_error
from sklearn.neural_network import MLPRegressor
from sklearn.preprocessing import OneHotEncoder, MinMaxScaler

random.seed(0)

def get_data():
    conn = pymysql.connect(host='localhost',  # 连接名称
                           user='root',  # 用户名
                           passwd='q19723011',  # 密码
                           port=3306,  # 端口，默认为3306
                           db='month6',  # 数据库
                           charset='utf8',  # 字符编码
                           )
    cur = conn.cursor()  # 生成游标对象
    sql = "select * from stationflow  "
    cur.execute(sql)
    datas = list(cur.fetchall())
    conn.commit()
    cur.close()  # 关闭游标
    conn.close()  # 关闭连接
    return datas


def train(datas):
    # 处理数据，划分训练集，测试集，归一化处理，独热编码处理
    data_count = len(datas)
    random.shuffle(datas)  # 打乱数据
    datas = np.array(datas)
    data_case = datas[:, 0:3]  # 获取特征值
    data_label = datas[:, 3:4]  # 获取标签
    mm = MinMaxScaler()
    data_label_process = mm.fit_transform(data_label)  # 对数据归一化处理
    enc = OneHotEncoder(sparse=False)
    enc.fit(data_case)
    data_case_hot = enc.transform(data_case)  # 对特征值进行独热编码
    # 以7:3划分训练集和测试集
    train_data_case = data_case_hot[0:int(data_count * 0.7)]
    train_data_label = data_label_process[0:int(data_count * 0.7)]
    test_data_case = data_case_hot[int(data_count * 0.7):]
    test_data_label = data_label_process[int(data_count * 0.7):]
    # 训练模型
    model = MLPRegressor(hidden_layer_sizes=(8,8,8), activation='tanh', solver='adam', max_iter=2000,
                         learning_rate='adaptive', learning_rate_init=0.02)  # BP神经网络回归模型
    model.fit(train_data_case, train_data_label.ravel())  # 训练模型
    pre_train = model.predict(train_data_case)  # 模型训练集预测
    pre_test = model.predict(test_data_case)    # 模型测试机预测
    pre = mm.inverse_transform(np.append(pre_train, pre_test).reshape(1, -1))[0]  # 反归一化
    print("训练集的效果")
    for i in range(len(pre_train)):
        print(pre[i], data_label[i])

    print("测试集的效果")
    for i in range(len(pre_test)):
        print(pre[i+len(pre_train)], data_label[i+len(pre_train)])
    print(mean_absolute_error(pre, data_label))


if __name__ == '__main__':
    datas = get_data()
    train(datas)
